package dao;

import java.sql.*;

public class Query {
    private String USERNAME = "seng403user";
    private String PASSWORD = "seng403ftw";
    private String URL = "jdbc:mysql://70.72.170.157:3306/suite_dreams";
	
    // declare a connection by using Connection interface 
    Connection connection = null;

    // declare object of Statement interface that uses for executing sql statements.
    Statement statement = null;

    // declare a ResultSet that is used as a table for output data from that table.
    ResultSet rs = null;
    
    /**
     * Opens a connection to the database.
     * @return 1 - connection successful, 0 - error occurred
     */
    public int openConnection() {
        try {
      	   // Establish a connection
             Class.forName ("com.mysql.jdbc.Driver").newInstance ();
             connection = DriverManager.getConnection (URL, USERNAME, PASSWORD);
		 
             System.out.println ("Database connection established");
			} catch (Exception e) {
				System.err.println ("ERROR: Cannot connect to database server");
				System.out.println(e);
				return 0;
			}
			return 1;
    }
    
    /**
     * Executes a select query against the database
     * @param query		query to be executed
     * @return a ResultSet object that contains the data produced by the given query; never null 
     */
    public ResultSet executeSelectStatement(String query) {
    	if(query != null) {
	    	try {
		        /* createStatement() is used for create statement object that is used for sending sql statements
		        to the specified database. */
		        statement = connection.createStatement();
		        
		        // Execute query
		        rs = statement.executeQuery(query);
	    	} catch (Exception e) {
	    		System.err.println("ERROR: Problem executing select query");
				System.out.println(e);
	    	}
    	} else 
    		System.err.println("ERROR: The query is empty");
		return rs;
    }
    
    /**
     * Executes a insert query against the database
     * @param query		query to be executed
     * @return either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing (3) -1 an error has occured
     */
    public int executeInsertStatement(String query) {
		int updateQuery = 0;
    	
    	if(query != null) {
	    	try {
		        /* createStatement() is used for create statement object that is used for sending sql statements
		        to the specified database. */
		        statement = connection.createStatement();
		        
		        /* string out special characters from the query */
		        //query = query.replace("'", "");
		        //query = query.replace("\"", "");
		        //query = query.replace("-", "");
		        
		        System.out.println(query);
		        
		        // Execute query
		        updateQuery = statement.executeUpdate(query);
		        
	    	} catch (Exception e) {
	    		System.err.println("ERROR: Problem with the insert query");
				return -1;
	    	}
    	} else 
    		System.err.println("ERROR: The query is empty");
		return updateQuery;
    }
    
    /**
     * Executes a insert or update query against the database
     * @param query		query to be executed
     * @return	either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing 
     */
    public int executeUpdateStatement(String query) {
		int updateQuery = 0;
    	
    	if(query != null) {
	    	try {
		        /* createStatement() is used for create statement object that is used for sending sql statements
		        to the specified database. */
		        statement = connection.createStatement();
		        
		        // Execute query
		        updateQuery = statement.executeUpdate(query);
		        
	    	} catch (Exception e) {
	    		System.err.println("ERROR: Problem with the update query");
				System.out.println(e);
	    	}
    	} else 
    		System.err.println("ERROR: The query is empty");
		return updateQuery;
    }
    
    /**
     * Executes a insert or update query against the database
     * @param query		query to be executed
     * @return	either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing 
     */
    public int executeDeleteStatement(String query) {
		int updateQuery = 0;
    	
    	if(query != null) {
	    	try {
		        /* createStatement() is used for create statement object that is used for sending sql statements
		        to the specified database. */
		        statement = connection.createStatement();
		        
		        // Execute query
		        updateQuery = statement.executeUpdate(query);
		        
	    	} catch (Exception e) {
	    		System.err.println("ERROR: Problem with the delete query");
				System.out.println(e);
	    	}
    	} else 
    		System.err.println("ERROR: The query is empty");
		return updateQuery;
    }
    
    /**
     * Closes an existing connection to the database.
     * @return 1 - connection successful, 0 - error occurred
     */
    public int closeConnection() {
        try
        {
            // close all the connections.
            connection.close ();
            rs.close();
            statement.close();
            System.out.println ("Database connection terminated");
        }
        catch (Exception e) { 
        	System.err.println("ERROR: Problem with closing the connection");
        	System.out.println(e);
        	return 0;
        }
        return 1;
    }
    
	void startTest() {
		this.executeUpdateStatement("BEGIN;");
	}
	
	void endTest() {
		this.executeUpdateStatement("ROLLBACK;");
	}

}
